Generated code - Fetching DataReaders and projections, Adapter
Preface
Besides using Linq or
QuerySpec,
LLBLGen Pro has two other ways of fetching a resultset of fields: as an open
IDataReader object and as a
projection. This section discusses both and
offers examples for each of them, either using a stored procedure or a query build using entity fields. Fetching a resultset as an open IDataReader
is considered an advanced feature and should be used with care: an open
IDataReader
object represents an open cursor to data on a connected RDBMS over an open
connection. This means that passing the
IDataReader around in your application is not recommended. Instead use the
IDataReader in the method where you also called the
fetch logic to create it and immediately after that make sure the
IDataReader
gets closed and disposed. This way you're sure you'll free up resources early.
To understand projections better, it's recommended to first read the section about fetching an open
IDataReader. Another section describing projections, but then
related to an entity view object, is
Generated code - using the EntityView2 class.
Although QuerySpec and Linq offer ways to fetch projections into
objects, this section primarily discusses projections using the low-level
API. QuerySpec also supports fetching a projection as
IDataReader.
An example of this is given below in the section about fetching a
dynamic list as
IDataReader.
Fetching a resultset as an open IDataReader
To fetch a resultset as an open
IDataReader, you call one of the overloads of
FetchDataReader, a method of
DataAccessAdapter. There are two ways to
use the
FetchDataReader method: by supplying a ready to use
IRetrievalQuery
or by specifying a fields list, and various other elements which are required for
creating a new query by the Dynamic Query Engine (DQE). The first option, the
IRetrievalQuery option, can be used to fetch a retrieval stored procedure
as an open
IDataReader, by using the
RetrievalProcedures.
GetStoredProcedureNameCallAsQuery() method of the particular stored procedure call. This is a
generated method, one for every retrieval stored procedure call known in the LLBLGen Pro project.
FetchDataReader accepts also a parameter called
CommandBehavior. This parameter is very important as it controls
the behavior the datareader should perform when the datareader is closed. It's only required to specify a behavior different than
CloseConnection if the fetch is
inside a transaction and the connection has to stay open after the datareader has been closed.
It's possible to construct your own
IRetrievalQuery object with your own SQL, by instantiating a new
RetrievalQuery object. However in general, it's recommended
to use the
FetchDataReader overloads which accept a fieldslist and other elements and let LLBLGen Pro generate the query for you.
Fetching a Retrieval Stored Procedure as an IDataReader
An example of calling a procedure and receive a datareader from it is enlisted below. It calls the Northwind stored procedure
CustOrdersOrders which
returns a single resultset with 4 fields. The example simply prints the output on the console.
// C#
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
IDataReader reader = adapter.FetchDataReader(
RetrievalProcedures.GetCustOrdersOrdersCallAsQuery( "CHOPS" ),
CommandBehavior.CloseConnection );
while( reader.Read() )
{
Console.WriteLine( "Row: {0} | {1} | {2} | {3} |", reader.GetValue( 0 ),
reader.GetValue( 1 ), reader.GetValue( 2 ), reader.GetValue( 3 ) );
}
// close reader, will also close connection
reader.Close();
}
' VB.NET
Using adapter As New DataAccessAdapter()
Dim reader as IDataReader = adapter.FetchDataReader( _
RetrievalProcedures.GetCustOrdersOrdersCallAsQuery( "CHOPS" ), _
CommandBehavior.CloseConnection )
While reader.Read()
Console.WriteLine( "Row: {0} | {1} | {2} | {3} |", reader.GetValue( 0 ), _
reader.GetValue( 1 ), reader.GetValue( 2 ), reader.GetValue( 3 ) )
End While
' close reader, will also close connection
reader.Close()
End Using
Fetching a Dynamic List as an IDataReader
An example of a dynamic list which is used to receive a datareader from it is enlisted below. The example simply prints the output on the console.
- Low-level API, C#
- Low-level API, VB.NET
- QuerySpec, C#
- QuerySpec, VB.NET
// Low-level API, C#
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
ResultsetFields fields = new ResultsetFields( 3 );
// simply set the fields in the indexes, which will use the field name for the column name
fields[0] = CustomerFields.CustomerId;
fields[1] = CustomerFields.CompanyName;
fields[2] = OrderFields.OrderId;
RelationPredicateBucket filter = new RelationPredicateBucket(CustomerFields.Country == "Germany");
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId);
IDataReader reader = adapter.FetchDataReader( fields, filter, CommandBehavior.CloseConnection, 0, true );
while( reader.Read() )
{
Console.WriteLine( "Row: {0} | {1} | {2} |",
reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) );
}
reader.Close();
}
' VB.NET
Using adapter As New DataAccessAdapter()
Dim fields As New ResultsetFields( 3 )
' simply set the fields in the indexes, which will use the field name for the column name
fields(0) = CustomerFields.CustomerId
fields(1) = CustomerFields.CompanyName
fields(2) = OrderFields.OrderId
Dim filter As New RelationPredicateBucket()
filter.PredicateExpression.Add( _
New FieldCompareValuePredicate(CustomerFields.Country, Nothing, ComparisionOperator.Equal, "Germany"))
filter.Relations.Add(CustomerEntity.Relations.OrderEntityUsingCustomerId)
Dim reader As IDataReader = adapter.FetchDataReader( fields, filter, CommandBehavior.CloseConnection, 0, True )
While reader.Read()
Console.WriteLine( "Row: {0} | {1} | {2} |", _
reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) )
End While
reader.Close()
End Using
// QuerySpec, C#
using(var adapter = new DataAccessAdapter())
{
var qf = new QueryFactory();
var q = qf.Create()
.Select(CustomerFields.CustomerId, CustomerFields.CompanyName, OrderFields.OrderId)
.From(qf.Customer.InnerJoin(qf.Order).On(CustomerFields.CustomerId == OrderFields.CustomerId))
.Where(CustomerFields.Country == "Germany");
var reader = adapter.FetchAsDataReader(q, CommandBehavior.CloseConnection);
while(reader.Read())
{
Console.WriteLine("Row: {0} | {1} | {2} |",
reader.GetValue(0), reader.GetValue(1), reader.GetValue(2));
}
reader.Close();
}
' QuerySpec, VB.NET
Using adapter As New DataAccessAdapter()
Dim qf As New QueryFactory()
Dim q = qf.Create() _
.Select(CustomerFields.CustomerId, CustomerFields.CompanyName, OrderFields.OrderId) _
.From(qf.Customer.InnerJoin(qf.Order).On(CustomerFields.CustomerId = OrderFields.CustomerId)) _
.Where(CustomerFields.Country = "Germany")
Dim reader = adapter.FetchAsDataReader(q, CommandBehavior.CloseConnection)
While reader.Read()
Console.WriteLine( "Row: {0} | {1} | {2} |", _
reader.GetValue( 0 ), reader.GetValue( 1 ), reader.GetValue( 2 ) )
End While
reader.Close()
End Using
Resultset projections
In the previous section we've seen that a query could be fetched as an open IDataReader, where the query could be an IRetrievalQuery object containing a stored
procedure call, or a dynamic formulated query from fields, a filter and other elements you might want to use in the query. It is then up to you what to do with the
IDataReader. It's likely you'll want to
project the data available to you through the IDataReader object onto a data-structure.
Projecting a resultset is a
term from the relational algebra, the Wikipedia has a formal explanation of it:
Projection (relational algebra) (opens in a new window).
It comes down to the fact that you create a new set of data from an existing set of data. The existing set of data is the resultset you want to project. The new
set is the projection result.
LLBLGen Pro offers two different projection mechanisms: projecting an
EntityView2<T> (see:
Generated code - using the EntityView2 class)
and projecting a fetched resultset, which is discussed here. Both mechanisms
are roughly the same, only the source data origin differs and the used
interface implemented by the used projection engine. The projections of
entity view data are a little more advanced because it's possible to execute
in-memory filters on the entity object itself to make a selection which
field to project.
For projections of
EntityView2<T> data,
EntityPropertyProjector objects are used, for projections of resultset data, the more simpler
DataValueProjector objects are used. Their meaning is roughly the same, so if you're familiar with
EntityView2<T> projections, you'll directly understand the
examples below using
DataValueProjector objects. As the projection engine interfaces required for both mechanisms are fairly similar, the
shipped projection engines thereby can be used for both mechanisms.
Resultset projections are done by an
IGeneralDataProjector implementation.
IGeneralDataProjector allows an object[] array of values to be projected onto new
instances of whatever class is supported by the
IGeneralDataProjector implementation, for example new entities or a
DataRow in a
DataTable.
Which values in the object[] array are projected onto which properties of the target element, created by the
IGeneralDataProjector implementation, is specified
by the specified set of
IDataValueProjector implementations passed
in.
In Adapter, the
DataAccessAdapter class has a method called
FetchProjection with various overloads. This method produces the projection of the resultset
defined by the input parameters (similar to the
FetchDataReader method) or the resultset passed in in the form of an open
IDataReader object.
By which projection engine the projection is performed as well which data is projected is passed in as well.
FetchProjection doesn't return a value, the result is in the projection engine object. This method has similar overloads as
FetchDataReader, though it
doesn't accept a CommandBehavior: if a connection is open, it leaves it open, if no connection is open, it creates one and closes
one afterwards.
Projecting Stored Procedure resultset onto entity collection
For this stored procedure projection example, the following stored procedure is used:
CREATE procedure pr_CustomersAndOrdersOnCountry
@country VARCHAR(50)
AS
SELECT * FROM Customers WHERE Country = @country
SELECT * FROM Orders WHERE CustomerID IN
(
SELECT CustomerID FROM Customers WHERE Country = @country
)
which is a SQL Server stored procedure and which returns 2 resultsets: the first is all customers filtered on a given Country, and the
second is all orders of those filtered customers.
The stored procedure is fetched as an open IDataReader and both resultsets are projected onto entity collections: the first resultset on an EntityCollection
object with CustomerEntity instances and the second on an EntityCollection of OrderEntity
instances. The stored procedure uses a wildcard select list. This is for
simplicity.
// C#
EntityCollection<CustomerEntity> customers = new EntityCollection<CustomerEntity>();
EntityCollection<OrderEntity> orders = new EntityCollection<OrderEntity>();
using(IRetrievalQuery query = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery( "Germany" ))
{
using(DataAccessAdapter adapter = new DataAccessAdapter())
{
using(IDataReader reader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
{
// first resultset: Customers.
List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
// project value on index 0 in resultset row onto CustomerId
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, typeof( string ) ) );
// project value on index 1 in resultset row onto CompanyName
valueProjectors.Add( new DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, typeof( string ) ) );
// resultset contains more rows, we just project those 2. The rest is trivial.
DataProjectorToIEntityCollection2 projector = new DataProjectorToIEntityCollection2( customers );
adapter.FetchProjection( valueProjectors, projector, reader );
// second resultset: Orders.
valueProjectors = new ArrayList();
valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderId.ToString(), 0, typeof( int ) ) );
valueProjectors.Add( new DataValueProjector( OrderFieldIndex.CustomerId.ToString(), 1, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( OrderFieldIndex.OrderDate.ToString(), 3, typeof( DateTime ) ) );
// switch to the next resultset in the datareader
reader.NextResult();
projector = new DataProjectorToIEntityCollection2( orders );
adapter.FetchProjection( valueProjectors, projector, reader );
reader.Close();
}
}
}
' VB.NET
Dim customers As New EntityCollection(Of CustomerEntity)()
Dim orders As New EntityCollection(Of OrderEntity)()
Using query As IRetrievalQuery = RetrievalProcedures.GetCustomersAndOrdersOnCountryCallAsQuery( "Germany" )
Using adapter As New DataAccessAdapter()
Using reader As IDataReader = adapter.FetchDataReader(query, CommandBehavior.CloseConnection))
' first resultset: Customers.
Dim valueProjectors As New List(Of IDataValueProjector)()
' project value on index 0 in resultset row onto CustomerId
valueProjectors.Add( New DataValueProjector( CustomerFieldIndex.CustomerId.ToString(), 0, GetType( Sring ) ) )
' project value on index 1 in resultset row onto CompanyName
valueProjectors.Add( New DataValueProjector( CustomerFieldIndex.CompanyName.ToString(), 1, GetType( String ) ) )
' resultset contains more rows, we just project those 2. The rest is trivial.
Dim projector As New DataProjectorToIEntityCollection2( customers )
adapter.FetchProjection( valueProjectors, projector, reader )
' second resultset: Orders.
valueProjectors = New ArrayList()
valueProjectors.Add( New DataValueProjector( OrderFieldIndex.OrderId.ToString(), 0, GetType( Integer ) ) )
valueProjectors.Add( New DataValueProjector( OrderFieldIndex.CustomerId.ToString(), 1, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( OrderFieldIndex.OrderDate.ToString(), 3, GetType( DateTime ) ) )
' switch to the next resultset in the datareader
reader.NextResult()
projector = New DataProjectorToIEntityCollection2( orders )
adapter.FetchProjection( valueProjectors, projector, reader )
reader.Close()
End Using
End Using
End Using
Projecting Dynamic List resultset onto custom classes
We can go one step further and create a fetch of a dynamic list and fill a list of custom class instances, for example for transportation by a Webservice and
you want lightweight Data Transfer Objects (DTO). For clarity, the Linq and
QuerySpec alternatives are given as well.
- Low-level API, C#
- Low-level API, VB.NET
- QuerySpec, C#
- QuerySpec, VB.NET
- Linq, C#
- Linq, VB.NET
// Low-level API, C#
List<CustomCustomer> customClasses = new List<CustomCustomer>();
ResultsetFields fields = new ResultsetFields( 4 );
fields[0] = CustomerFields.City;
fields[1] = CustomerFields.CompanyName;
fields[2] = CustomerFields.CustomerId;
fields[3] = CustomerFields.Country;
DataProjectorToCustomClass<CustomCustomer> projector =
new DataProjectorToCustomClass<CustomCustomer>( customClasses );
// Define the projections of the fields.
List<IDataValueProjector> valueProjectors = new List<IDataValueProjector>();
valueProjectors.Add( new DataValueProjector( "City", 0, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "CompanyName", 1, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "CustomerID", 2, typeof( string ) ) );
valueProjectors.Add( new DataValueProjector( "Country", 3, typeof( string ) ) );
// perform the fetch combined with the projection.
using( DataAccessAdapter adapter = new DataAccessAdapter() )
{
adapter.FetchProjection( valueProjectors, projector, fields, null, 0, true );
}
' Low-level API, VB.NET
Dim customClasses As New List(Of CustomCustomer)()
Dim fields As New ResultsetFields( 4 )
fields(0) = CustomerFields.City
fields(1) = CustomerFields.CompanyName
fields(2) = CustomerFields.CustomerId
fields(3) = CustomerFields.Country
Dim projector As New DataProjectorToCustomClass(Of CustomCustomer)( customClasses )
' Define the projections of the fields.
Dim valueProjectors As New List(Of IDataValueProjector)()
valueProjectors.Add( New DataValueProjector( "City", 0, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "CompanyName", 1, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "CustomerID", 2, GetType( String ) ) )
valueProjectors.Add( New DataValueProjector( "Country", 3, GetType( String ) ) )
' perform the fetch combined with the projection.
Using adapter As New DataAccessAdapter()
adapter.FetchProjection( valueProjectors, projector, fields, Nothing, 0, True )
End Using
// QuerySpec, C#
List<CustomCustomer> customClasses;
using(var adapter = new DataAccessAdapter())
{
var qf = new QueryFactory();
var q = qf.Customer
.Select(() => new CustomCustomer()
{
City = CustomerFields.City.ToValue<string>(),
CompanyName = CustomerFields.CompanyName.ToValue<string>(),
Country = CustomerFields.Country.ToValue<string>(),
CustomerID = CustomerFields.CustomerId.ToValue<string>()
});
customClasses = adapter.FetchQuery(q);
}
' QuerySpec, VB.NET
Dim customClasses As List(Of CustomCustomer)
Using adapter As New DataAccessAdapter()
Dim qf As New QueryFactory()
Dim q = qf.Customer _
.Select(Function () New CustomCustomer() With _
{
.City = CustomerFields.City.ToValue(Of String)(), _
.CompanyName = CustomerFields.CompanyName.ToValue(Of String)(), _
.Country = CustomerFields.Country.ToValue(Of String)(), _
.CustomerID = CustomerFields.CustomerId.ToValue(Of String)() _
})
customClasses = adapter.FetchQuery(q)
End Using
// Linq, C#
List<CustomCustomer> customClasses;
using(var adapter = new DataAccessAdapter())
{
var metaData = new LinqMetaData(adapter);
var q = from c in metaData.Customer
select new CustomCustomer()
{
CustomerID = c.CustomerId,
CompanyName = c.CompanyName,
Country = c.Country,
City = c.City
};
customClasses = q.ToList();
}
' Linq, VB.NET
Dim customClasses As List(Of CustomCustomer)
Using adapter As New DataAccessAdapter()
Dim metaData As New LinqMetaData(adapter)
Dim q2 = From c In metaData.Customer _
Select New CustomCustomer() With
{
.CustomerID = c.CustomerId,
.CompanyName = c.CompanyName,
.Country = c.Country,
.City = c.City
}
customClasses = q.ToList()
End Using
Where the custom class is:
public class CustomCustomer
{
#region Class Member Declarations
private string _customerID, _companyName, _city, _country;
#endregion
public CustomCustomer()
{
_city = string.Empty;
_companyName = string.Empty;
_customerID = string.Empty;
_country = string.Empty;
}
#region Class Property Declarations
public string CustomerID
{
get { return _customerID; }
set { _customerID = value; }
}
public string City
{
get { return _city; }
set { _city = value; }
}
public string CompanyName
{
get { return _companyName; }
set { _companyName = value; }
}
public string Country
{
get { return _country; }
set { _country = value; }
}
#endregion
}